package dao;

import beans.Compra;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class CompraDAO {

	PreparedStatement pst;
	ResultSet rs;

	public CompraDAO() {
	}

	public Compra load(int id) {
		Compra cmp = new Compra();

		String query = "SELECT * FROM compra WHERE id = ?";

		try {
			pst = Conexao.getConexao().prepareStatement(query);
			pst.setInt(1, id);

			rs = pst.executeQuery();

			if (rs.next()) {
				cmp.setId(rs.getInt("id"));
				cmp.setIdDespesa(rs.getInt("id_desp"));
				cmp.setItem(rs.getString("item"));
				cmp.setPrecoUnitario(rs.getFloat("preco_unitario"));
				cmp.setDataPedido(rs.getDate("data_pedido"));
				cmp.setQuantidade(rs.getInt("quantidade"));
				cmp.setStatus(rs.getString("status"));
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}

		return cmp;
	}

	public void insert(Compra cmp) {
		String query = "INSERT INTO compra (id_desp, item, preco_unitario, data_pedido, quantidade, status)"
				+ " VALUES(?, ?, ?, ?, ?, ?)";

		try {
			pst = Conexao.getConexao().prepareStatement(query);
			;
			pst.setInt(1, cmp.getIdDespesa());
			pst.setString(2, cmp.getItem());
			pst.setFloat(3, cmp.getPrecoUnitario());
			pst.setDate(4, cmp.getDataPedido());
			pst.setInt(5, cmp.getQuantidade());
			pst.setString(6, cmp.getStatus());

			pst.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}
	}

	private void update(Compra cmp) {
		String query = "UPDATE compra"
				+ " SET item = ?, precoUnitario = ?, dataPedido = ?, quantidade = ?, status = ?"
				+ " WHERE id = ?";

		try {
			pst = Conexao.getConexao().prepareStatement(query);

			pst.setString(1, cmp.getItem());
			pst.setFloat(2, cmp.getPrecoUnitario());
			pst.setDate(3, cmp.getDataPedido());
			pst.setInt(4, cmp.getQuantidade());
			pst.setString(5, cmp.getStatus());
			pst.setInt(6, cmp.getId());

			pst.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}


	}

	public int save(Compra cmp) {
		String query = "SELECT COUNT(*) FROM compra WHERE id = ?";
		int serialNum = 0;
		try {
			pst = Conexao.getConexao().prepareStatement(query);
			pst.setInt(1, cmp.getId());

			rs = pst.executeQuery();

			rs.next();

			if (rs.getInt("count") == 0) {
				insert(cmp);
			} else {
				update(cmp);
			}

			query = "SELECT currval('compra_id_seq')";
			pst = Conexao.getConexao().prepareStatement(query);
			rs = pst.executeQuery();

			if (rs.next()) {
				serialNum = rs.getInt(1);
			}


		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}
		return serialNum;
	}

	public void delete(Compra cmp) {
		String query = "DELETE FROM compra WHERE id = ?";

		try {
			pst = Conexao.getConexao().prepareStatement(query);
			pst.setInt(1, cmp.getId());

			pst.executeUpdate();

		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}
	}

	public ArrayList<Compra> loadAll() {
		ArrayList<Compra> compras = new ArrayList<Compra>();

		String query = "SELECT * FROM compra";

		try {
			pst = Conexao.getConexao().prepareStatement(query);
			rs = pst.executeQuery();

			while (rs.next()) {
				Compra cmp = new Compra();

				cmp.setId(rs.getInt("id"));
				cmp.setIdDespesa(rs.getInt("idDespesa"));
				cmp.setItem(rs.getString("item"));
				cmp.setPrecoUnitario(rs.getFloat("precoUnitario"));
				cmp.setDataPedido(rs.getDate("dataPedido"));
				cmp.setQuantidade(rs.getInt("quantidade"));
				cmp.setStatus(rs.getString("status"));


				compras.add(cmp);
			}
		} catch (SQLException ex) {
			ex.getLocalizedMessage();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}

		return compras;
	}
}
